<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
  <title id="cj20941">CREATE MATERIALIZED VIEW</title>
  <body>
    <p id="sql_command_desc">Defines a new materialized view.</p>
    <section id="section1">
      <title>Synopsis</title>
      <codeblock id="sql_command_synopsis">CREATE MATERIALIZED VIEW <varname>table_name</varname>
    [ (<varname>column_name</varname> [, ...] ) ]
    [ WITH ( <varname>storage_parameter</varname> [= <varname>value</varname>] [, ... ] ) ]
    [ TABLESPACE <varname>tablespace_name</varname> ]
    AS <varname>query</varname>
    [ WITH [ NO ] DATA ]
    [DISTRIBUTED {| BY <varname>column</varname> [<varname>opclass</varname>], [ ... ] | RANDOMLY | REPLICATED }]</codeblock>
    </section>
    <section id="section3">
      <title>Description</title>
      <p><codeph>CREATE MATERIALIZED VIEW</codeph> defines a materialized view of a query. The query
        is run and used to populate the view at the time the command is issued (unless
          <codeph>WITH NO DATA</codeph> is used) and can be refreshed using <codeph>REFRESH
          MATERIALIZED VIEW</codeph>.</p>
      <p><codeph>CREATE MATERIALIZED VIEW</codeph> is similar to <codeph>CREATE TABLE AS</codeph>,
        except that it also remembers the query used to initialize the view, so that it can be
        refreshed later upon demand. To refresh materialized view data, use the <codeph>REFRESH
          MATERIALIZED VIEW</codeph> command. A materialized view has many of the same properties as
        a table, but there is no support for temporary materialized views or automatic generation of
        OIDs. </p>
    </section>
    <section id="section4">
      <title>Parameters</title>
      <parml>
        <plentry>
          <pt><varname>table_name</varname></pt>
          <pd>The name (optionally schema-qualified) of the materialized view to be created.</pd>
        </plentry>
        <plentry>
          <pt><varname>column_name</varname></pt>
          <pd>The name of a column in the materialized view. The column names are assigned based on
            position. The first column name is assigned to the first column of the query result, and
            so on. If a column name is not provided, it is taken from the output column names of the
            query.</pd>
        </plentry>
        <plentry>
          <pt>WITH ( <varname>storage_parameter</varname> [= <varname>value</varname>] [, ... ]
            )</pt>
          <pd>This clause specifies optional storage parameters for the materialized view. All
            parameters supported for <codeph>CREATE TABLE</codeph> are also supported for
              <codeph>CREATE MATERIALIZED VIEW</codeph> with the exception of OIDS. See
                <codeph><xref href="CREATE_TABLE.xml"/></codeph> for more information.</pd>
        </plentry>
        <plentry>
          <pt>TABLESPACE <varname>tablespace_name</varname></pt>
          <pd>The <varname>tablespace_name</varname> is the name of the tablespace in which the new
            materialized view is to be created. If not specified, server configuration parameter
                <codeph><xref href="../config_params/guc-list.xml#default_tablespace"/></codeph> is
            consulted.</pd>
        </plentry>
        <plentry>
          <pt>query</pt>
          <pd>A <codeph><xref href="SELECT.xml"/></codeph> or <codeph><xref href="VALUES.xml"
              /></codeph> command. This query will run within a security-restricted operation; in
            particular, calls to functions that themselves create temporary tables will fail.</pd>
        </plentry>
        <plentry>
          <pt>WITH [ NO ] DATA</pt>
          <pd>This clause specifies whether or not the materialized view should be populated with
            data at creation time. <codeph>WITH DATA</codeph> is the default, populate the
            materialized view. For <codeph>WITH NO DATA</codeph>, the materialized view is not
            populated with data, is flagged as unscannable, and cannot be queried until
              <codeph>REFRESH MATERIALIZED VIEW</codeph> is used to populate the materialized view.
            An error is returned if a query attempts to access an unscannable materialized
            view.</pd>
        </plentry>
        <plentry>
          <pt>DISTRIBUTED BY (column [opclass], [ ... ] )</pt>
          <pt>DISTRIBUTED RANDOMLY</pt>
          <pt>DISTRIBUTED REPLICATED</pt>
          <pd>Used to declare the Greenplum Database distribution policy for the materialized view
            data. For information about a table distribution policy, see <codeph><xref
                href="CREATE_TABLE.xml"/></codeph>.</pd>
        </plentry>
      </parml>
    </section>
    <section id="section5">
      <title>Notes</title>
      <p>Materialized views are read only. The system will not allow an <codeph>INSERT</codeph>,
          <codeph>UPDATE</codeph>, or <codeph>DELETE</codeph> on a materialized view. Use
          <codeph>REFRESH MATERIALIZED VIEW</codeph> to update the materialized view data.</p>
      <p>If you want the data to be ordered upon generation, you must use an <codeph>ORDER
          BY</codeph> clause in the materialized view query. However, if a materialized view query
        contains an <codeph>ORDER BY</codeph> or <codeph>SORT</codeph> clause, the data is not
        guaranteed to be ordered or sorted if <codeph>SELECT</codeph> is performed on the
        materialized view.</p>
    </section>
    <section id="section6">
      <title>Examples</title>
      <p>Create a view consisting of all comedy films: </p>
      <codeblock>CREATE MATERIALIZED VIEW comedies AS SELECT * FROM films 
WHERE kind = 'comedy';</codeblock>
      <p>This will create a view containing the columns that are in the <codeph>film</codeph> table
        at the time of view creation. Though <codeph>*</codeph> was used to create the materialized
        view, columns added later to the table will not be part of the view. </p>
      <p>Create a view that gets the top ten ranked baby names:</p>
      <codeblock>CREATE MATERIALIZED VIEW topten AS SELECT name, rank, gender, year FROM 
names, rank WHERE rank &lt; '11' AND names.id=rank.id;</codeblock>
    </section>
    <section id="section7">
      <title>Compatibility</title>
      <p><codeph>CREATE MATERIALIZED VIEW</codeph> is a Greenplum Database extension of the SQL
        standard.</p>
    </section>
    <section id="section8">
      <title>See Also</title>
      <p><codeph><xref href="SELECT.xml" type="topic" format="dita"/></codeph>, <codeph><xref
            href="VALUES.xml"/></codeph>, <codeph><xref href="CREATE_VIEW.xml"/></codeph>,
            <codeph><xref href="ALTER_MATERIALIZED_VIEW.xml">ALTER MATERIALIZED
        VIEW</xref></codeph>, <codeph><xref href="DROP_MATERIALIZED_VIEW.xml">DROP MATERIALIZED
            VIEW</xref></codeph>, <codeph><xref href="REFRESH_MATERIALIZED_VIEW.xml"/></codeph></p>
    </section>
  </body>
</topic>
